Logical Operators

The Logical Operators in Oracle are AND, OR, and NOT. The logical operators are each teated differently with respect to the use of indexes and hash clusters.

The NOT operator

Any predicate within a NOT operator will not use an index or a hash cluster scan. The one exception to this is bitmap indexes.

The AND operator

The AND operator is the best for using indexes and hash cluster scans. If your WHERE clause contains only AND operators, then any one or more of the predicates could use and index.

The OR operator

Consider the SQL:

SELECT *
FROM  my_table
WHERE col1 = :x
OR    col2 = :y

If both col1 and col2 are indexed, then Oracle may choose to expand the SQL internally into:

SELECT *
FROM  my_table
WHERE col1 = :x
UNION
SELECT *
FROM  my_table
WHERE col2 = :y

If you run a SQL with OR predicates through Explain Plan, you may see that it is re-writing it as a UNION. If not, you can force this behaviour with the USE_CONCAT hint.

Expansion into UNIONs is the only way to use indexes or hash cluster scans with OR predicates. The one exception to this is bitmap indexes.

Tip

Don't do this

SELECT *
FROM  my_table
WHERE col1 = :a
OR    col1 = :b

Do this instead:

SELECT *
FROM  my_table
WHERE col1 IN (:a, :b)

In the first example, Oracle will expand it into a UNION which results in a sort. The second example can be performed in a single scan (Oracle calls this Inlist Iterator) and avoides the sort; much quicker on a large SELECT.